# Create your models here.
from django.contrib.auth.models import User
from django.template.loader import get_template
from config.conf import *
import smtplib  
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import MySQLdb


def getEmail(username):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select * from auth_user where username = '%s'" % (username)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    if(rows):
        row = rows[0]
        return row[4]
    else:
        return "not found"
    return "not found"

def insertUserToDB(username, password, email):
    try:
        user = User.objects.get(username=username)
    except:    
        user = User(username=username, email=email)
        user.set_password(password)
        user.save()
    return 
    
def insertUserInfo(username, phone, company, address):
    db = MySQLdb.connect('localhost', 'root', 'root', 'bookstore', charset="utf8", use_unicode=True)
    cursor = db.cursor()
    sql = "select * from auth_user where username = '%s'" % (username)
    cursor.execute(sql)
    rows = cursor.fetchall()
    if(rows):
        row = rows[0]
        id = row[0]       
        sql = """INSERT INTO user_info(id, phone, company, address) 
        VALUES ('%d', '%s', '%s', '%s')""" % (id, phone, company, address)
        cursor.execute(sql)
        db.commit()
        db.close()
        return
    else:
        return "not found"

    
def getFeaturedBook():
    db = initDataBase()
    cursor = db.cursor()
    featurePageSize = getFeaturePageSize()
    sql = "Select e.title, b.thumbnail,b.summary, b.nlike,b.nid from book as b, entity as e WHERE b.nid = e.nid AND b.feature = 1 LIMIT 0,%s" % (featurePageSize);
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows;
    
def getSpecialBook():
    db = initDataBase()
    cursor = db.cursor()
    sql = "Select e.title, b.thumbnail,b.summary, b.nlike,b.nid from book as b, entity as e WHERE b.nid = e.nid AND b.special = 1";
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows;

def getAllBook():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT b.thumbnail, e.title, b.special, e.created,b.nid  FROM book AS b, entity AS e WHERE b.nid = e.nid  ORDER BY e.created DESC "
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows;

def getBookFromId(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT b.thumbnail, b.author, b.summary, b.detail,  e.title  FROM book AS b, entity AS e WHERE b.nid = e.nid AND b.nid = %s" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    if(rows):
        return rows[0];
    return null
def getRelatedBook(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT nid from related_book WHERE related = %s" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows

def getBookForRelatedBox(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT b.thumbnail, e.title, b.special, e.created  FROM book AS b, entity AS e WHERE b.nid = e.nid AND b.nid = %s ORDER BY e.created DESC " % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]
    
    
def getNewBook():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT e.title, b.thumbnail,b.nid FROM entity AS e, book AS b WHERE b.nid = e.nid ORDER BY e.created DESC LIMIT 0,3";
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows;    
    
def getCategoryBook():
    db = initDataBase()
    cursor = db.cursor()
    sql = "Select * from category Order by name"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows;
    
def getFamousBook():
    db = initDataBase()
    cursor = db.cursor()
    sql = "Select e.title,b.thumbnail,b.author,b.nlike,b.price,b.point, b.nid from book as b, entity as e WHERE b.nid = e.nid Order by point desc LIMIT 0,20"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows;
        
def sendEmail(username, email):
    check = 0
    toaddrs = email
    fromaddr = 'ttcnpmgroup4@gmail.com'  
    msg = MIMEMultipart('alternative')
    msg['Subject'] = "Register Successful !"
    msg['from'] = fromaddr
    msg['to'] = toaddrs    
    html = open('templates/mail_template.html', 'rb') 
    content = MIMEText('Hi %s!' % (username) + html.read(), 'html')
    # Credentials (if needed)  
    name_fr = 'ttcnpmgroup4'  
    pass_fr = 'dongnamnam'        
    # The actual mail send 
    msg.attach(content)
    try:
        server = smtplib.SMTP('smtp.gmail.com:587')  
        server.starttls()  
        server.login(name_fr, pass_fr)
        server.sendmail(fromaddr, toaddrs, msg.as_string())
    except Exception:
        check = 1
        server.quit()
    return check

def getContact_Info(name, email, phone, company, message):
    db = MySQLdb.connect('localhost', 'root', 'root', 'bookstore', charset="utf8", use_unicode=True)
    cursor = db.cursor()
    sql = """INSERT INTO contact_info(name,email,phone,company,message)
        VALUES ('%s', '%s', '%s', '%s', '%s')""" % (name, email, phone, company, message)
    cursor.execute(sql)
    db.commit()
    db.close()
    return

def getBook_category(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT b.thumbnail, e.title, c.name, b.special, e.created ,b.nid FROM book AS b, category AS c, entity AS e WHERE b.cid = c.cid AND b.nid = e.nid AND c.cid = %s ORDER BY e.created DESC" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows
def getCategoryName(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT category.name FROM category WHERE cid = %s" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]
    
def getAboutUsStatic():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT detail FROM static WHERE value ='About Us'" 
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]
    
def getAboutOurStoreStatic():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT detail FROM static WHERE value ='About Our Store'" 
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]
    
def getServiceStatic():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT detail FROM static WHERE value ='Services'" 
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]
    
def getPrivacyStatic():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT detail FROM static WHERE value ='Privacy'" 
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]
    
def getFeaturePageSize():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT c.value FROM configuration as c WHERE c.key='FeaturePageSize'" 
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]
    
def getSpecialPageSize():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT c.value FROM configuration as c WHERE c.key='SpecialPageSize'" 
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]